PostgreSQL 9.5 : Replication Settings
2017/09/25 |
Configure PostgreSQL Replication settings. This configuration is Master-Slave settings.
|
|
[1] | |
[1] | Configure Master Host. |
[root@dlp ~]# postgresql-setup --initdb --unit rh-postgresql95-postgresql * Initializing database in '/var/opt/rh/rh-postgresql95/lib/pgsql/data' * Initialized, logs are in /var/lib/pgsql/initdb_rh-postgresql95-postgresql.log
[root@dlp ~]#
vi /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf # line 59: uncomment and change listen_addresses = ' * '
# line 173: uncomment and change wal_level = hot_standby
# line 180: uncomment and change # on ⇒ sync # remote_write ⇒ memory sync # local ⇒ slave is asynchronous # off ⇒ asynchronous synchronous_commit = local
# line 206: uncomment and change (enable archive_mode) archive_mode = on
# line 208: uncomment and change (command to get archives) archive_command = ' cp %p /var/opt/rh/rh-postgresql95/lib/pgsql/archive/%f '
# line 224: uncomment and change (slave servers + 1) max_wal_senders = 2
# line 226: uncomment and change wal_keep_segments = 10
# line 238: uncomment and change (any name you like) synchronous_standby_names = ' slave01 '
[root@dlp ~]#
vi /var/opt/rh/rh-postgresql95/lib/pgsql/data/pg_hba.conf # add to the end # host replication [replication user] [allowed IP addresses] password host replication replica 127.0.0.1/32 md5 host replication replica 10.0.0.30/32 md5 host replication replica 10.0.0.51/32 md5 /etc/rc.d/init.d/rh-postgresql95-postgresql start [root@dlp ~]# chkconfig rh-postgresql95-postgresql on -bash-4.1$ createuser --replication -P replica Enter password for new role: Enter it again: |
[2] | If IPTables is running, allow PostgreSQL service port. |
[root@dlp ~]# iptables -I INPUT 5 -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT
|
[3] | Configure Slave Host. |
[root@node01 ~]#
su - postgres # get backup from Master Server -bash-4.1$ pg_basebackup -h 10.0.0.30 -U replica -D /var/opt/rh/rh-postgresql95/lib/pgsql/data -P --xlog Password: # replica user's password
-bash-4.1$
vi /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf # line 247: uncomment and change hot_standby = on
-bash-4.1$
cp /opt/rh/rh-postgresql95/root/usr/share/pgsql/recovery.conf.sample /var/opt/rh/rh-postgresql95/lib/pgsql/data/recovery.conf -bash-4.1$ vi /var/opt/rh/rh-postgresql95/lib/pgsql/data/recovery.conf # line 44: uncomment and change (command to get archives) restore_command = ' scp 10.0.0.30:/var/opt/rh/rh-postgresql95/lib/pgsql/archive/%f %p '
# line 116: uncomment and change standby_mode = on
# line 123: uncomment and change (connection info to Master Server) primary_conninfo = ' host=10.0.0.30 port=5432 user=replica password=password application_name=slave01 '
exit logout [root@node01 ~]# /etc/rc.d/init.d/rh-postgresql95-postgresql start [root@node01 ~]# chkconfig rh-postgresql95-postgresql on |
[4] | It's OK all if result of the command below is like follows. Make sure the setting works normally to create databases on Master Server. |
-bash-4.1$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;" application_name | state | sync_priority | sync_state ------------------+-----------+---------------+------------ slave01 | streaming | 1 | sync (1 row) |